﻿/******************************************************   
创建者: 王东杰
创建时间: 2017-05-13
修改:替换查询店铺控件
修改时间: 2017-05-25
功能: 拆分店铺SKC
 ******************************************************/

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using george.Data;
using NHibernateVertica;
using PlanAPI.model;
using System.Data;
using System.Diagnostics;
using com.nm.library.Helper;
using com.nm.Helper;
using george.Helper;
using com.nm.library;

namespace PlanAPI
{
    [Description("拆分店铺SKC")]
    public class PLSplitShopSKCHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 获取数据库中的数据,不计算

        [MustLogin]
        [Description("拆分店铺SKC列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLSplitShopSKCModel.ListModel>>))]
        public ApiResult GetList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 100)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var count = ReturnCount(false, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, ShopIDs.GetString());
            var dt = ReturnList(false, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, ShopIDs.GetString(), Page, Size);
            var result = PaperData<IGrouping<string, PLSplitShopSKCModel.ListModel>>.Create(count, Size, Page);
            var list = ModelConvertHelper<PLSplitShopSKCModel.ListModel>.ConvertToModel(dt);

            result.Items =
                                    list.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                            d.ItemID + "_" + d.SKCQty + "_" + d.Weekv
                                            ).ToList();
            return Success(result);
        }

        #endregion 获取数据库中的数据,不计算

        #region 获取数据库中的数据,并进行计算

        [MustLogin]
        [Description("拆分店铺SKC列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLSplitShopSKCModel.ListModel>>))]
        public ApiResult GetComputedList(
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("类别")]string KindID = "",
            [ParamDescription("系列")]string SeriesID = "",
            [ParamDescription("商品层")]string MaterialProperty = "",
            [ParamDescription("品类")]string ModelID = "",
            [ParamDescription("小品类")]string ItemID = "",
            [ParamDescription("店铺shopid")]string ShopIDs = "",
            [ParamDescription("页码")] int Page = 1,
            [ParamDescription("页数")] int Size = 10)
        {
            var set = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (set == null) return Error("没有要修改企划的月份");
            var count = ReturnCount(true, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, ShopIDs.GetString());
            var dt = ReturnList(true, set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, ShopIDs.GetString(), Page, Size);
            //删除数据库中的数据
            var cmdList = DeleteComputedList2DB(set.Yearv.Value, set.Monthv.Value, ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, ShopIDs.GetString());
            Helper.ExecuteSqlTran(cmdList);
            if (dt?.Rows.Count > 0)
            {

                var result = PaperData<IGrouping<string, PLSplitShopSKCModel.ListModel>>.Create(count, count, Page);
                var list = ModelConvertHelper<PLSplitShopSKCModel.ListModel>.ConvertToModel(dt);

                result.Items =
                              list.GroupBy(
                                            d =>
                                                d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                                d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                                 d.ItemID + "_" + d.DataFlag
                                                ).ToList();
                var newList = ComputeAll(result);
                List<CommandInfo> cmdEmpty = new List<CommandInfo>();
                ExecuteSkcCalc(cmdEmpty, newList);
            }
            var resultList = GetList(ParentAreaID, KindID, SeriesID, MaterialProperty, ModelID, ItemID, ShopIDs, Page, Size);
            return resultList;
        }

        #endregion 获取数据库中的数据,并进行计算

        #region 返回数据列表

        /// <summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>        /// 返回拆分店铺SKC
        /// </summary>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public DataTable ReturnList(bool IsCompute, long Yearv, long Monthv, string ParentAreaID, string KindID,
            string SeriesID, string MaterialProperty, string ModelID, string ItemID, string ShopIDs, int Page, int Size)
        {
            try
            {

                var strSql = string.Empty;
                if (!IsCompute)
                {
                    #region 查询时返回数据列表


                    //如果只是展示列表,就查询出所有系列的数据

                    strSql = $@"
 		select
			s.Yearv,
			s.Monthv,
			s.ParentAreaID,
			shop.ShopName as ShopName,
			kind.KindName as KindName,
			series.SeriesName as SeriesName,
			s.MaterialProperty,
			model.ModelName as ModelName,
			s.Weekv,
			item.ItemName as ItemName,
			s.SKCQty,
			s.CkSKCQty,
			s.SeriesID,
			s.ItemID,
			shop.ShopCode as ShopCode,
			s.ShopID,
			s.KindID,
			s.ModelID,
			s.DataFlag
		from
ngoods.ngoods_PL_Item_SkcPcsDetail s
		LEFT JOIN ndw.ndw_shop shop ON s.ExtrSource = shop.ExtrSource and s.CompanyID=shop.CompanyID and
			s.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON  s.ExtrSource = dict.ExtrSource and s.CompanyID=dict.CompanyID and
			s.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on  s.ExtrSource = kind.ExtrSource and 
			s.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on  s.ExtrSource = model.ExtrSource and 
			s.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on  s.ExtrSource = series.ExtrSource and 
			s.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on s.ExtrSource = item.ExtrSource and 
			s.ItemID = item.ItemID  
		WHERE			
            s.ExtrSource='{UserInfo.UserExtrSource}' and s.CompanyID='{UserInfo.UserCompanyId}' and
			
			
			 s.Yearv={Yearv} and s.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and s.ParentAreaID = '{ParentAreaID}' ")}
{ (string.IsNullOrWhiteSpace(KindID) ? "" : $" and s.KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and s.SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and s.MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and s.ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and s.ItemID='{ItemID}' ")}
                    {
                        (string.IsNullOrWhiteSpace(ShopIDs)
                                               ? ""
                                               : $" and shop.shopid in {ShopIDs} ")}

order by
	s.Yearv,
	s.Monthv,
	s.ParentAreaID,
	s.ShopID,
	s.KindID,
	s.MaterialProperty,
	s.ModelID,
	s.SeriesID,
	s.ModelID,
	s.Weekv,
    s.DataFlag


LIMIT {Size} offset {(Page - 1.0) * Size}	
";


                    #endregion 查询时返回数据列表
                }
                else
                {
                    #region 计算时返回的数据列表
                    strSql = $@"
SELECT 

p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName as ShopName,
	p.KindName as KindName,
	p.SeriesName as SeriesName,
	p.MaterialProperty,
	p.ModelName as ModelName,
	p.Weekv,
	p.ItemName as ItemName,
	p.SKCQty,
	p.SeriesID,
	p.ItemID,
	p.ShopCode as ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID,
	p.CKMoveDeepQty,
	CASE k.dictName WHEN '共性款' THEN 0
	when '差异款' THEN 1 END as DataFlag,
	cast(
				k.dictValue as decimal(
					8,
					4
				)
			) as Rate

from (SELECT
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName as ShopName,
	p.KindName as KindName,
	p.SeriesName as SeriesName,
	p.MaterialProperty,
	p.ModelName as ModelName,
	p.Weekv,
	p.ItemName as ItemName,
	p.CkSKCQty as SKCQty,
	p.SeriesID,
	p.ItemID,
	p.ShopCode as ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID,
	p.CKMoveDeepQty
from
	(
		select
			s.Yearv,
			s.Monthv,
			s.ParentAreaID,
			shop.ShopName as ShopName,
			kind.KindName as KindName,
			series.SeriesName as SeriesName,
			s.MaterialProperty,
			model.ModelName as ModelName,
			s.Weekv,
			item.ItemName as ItemName,
			s.CkSKCQty,
			s.SeriesID,
			s.ItemID,
			shop.ShopCode as ShopCode,
			s.ShopID,
			s.KindID,
			s.ModelID,
            s.CKMoveDeepQty
		from
							ngoods.ngoods_PL_Item_SkcPcs s left join ndw.ndw_Shop shop on s.ExtrSource = shop.ExtrSource and s.CompanyID=shop.CompanyID and
			s.ShopID = shop.ShopID left join ndw.ndw_global_dictionary dict on s.ExtrSource = dict.ExtrSource and s.CompanyID=dict.CompanyID and
			s.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on  s.ExtrSource = kind.ExtrSource and 
			s.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on  s.ExtrSource = model.ExtrSource and 
			s.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on  s.ExtrSource = series.ExtrSource and 
			s.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on s.ExtrSource = item.ExtrSource and 
			s.ItemID = item.ItemID  
			WHERE			 
			   s.CkSKCQty > 0 and s.ExtrSource='{UserInfo.UserExtrSource}' and s.CompanyID='{UserInfo.UserCompanyId}' and
			
						   s.Yearv={Yearv} and s.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and s.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and s.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and s.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and s.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and s.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and s.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                        ? ""
                        : $" and shop.shopid in {ShopIDs} ")} 
		 
	) as p 
	group by
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName,
	p.KindName,
	p.SeriesName,
	p.MaterialProperty,
	p.ModelName,
	p.ItemName,
	p.Weekv,
	p.CkSKCQty,
	p.CKMoveDeepQty,
	p.SeriesID,
	p.ItemID,
	p.ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID
)as p cross join 
(select dictName,dictValue from dw.dw_global_dictionary
WHERE parentDictValue='全国共性款'
and isValid=0 ) as k
order by
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopID,
	p.KindID,
	p.MaterialProperty,
	p.ModelID,
	p.SeriesID,
	p.ModelID,
	p.Weekv

";
                    #endregion 计算时返回的数据列表
                }
                var cmd = Helper.GetSqlStringCommand(strSql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回数据列表

        #region 返回行数
        /// <summary>
        /// 返回拆分店铺SKC总条数
        /// </summary>
        /// <param name="ParentAreaID"></param>
        /// <param name="KindID"></param>
        /// <param name="SeriesID"></param>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <param name="Keys"></param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns></returns>
        public int ReturnCount(bool IsCompute, long Yearv, long Monthv, string ParentAreaID, string KindID, string SeriesID, string MaterialProperty, string ModelID, string ItemID, string ShopIDs)
        {
            try
            {
                var countSql = string.Empty;
                if (IsCompute)
                {
                    #region 计算时获取行数

                    countSql = $@"

--拆分店铺skc数量,获取行数

SELECT count(1) as COUNT from (SELECT
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName as ShopName,
	p.KindName as KindName,
	p.SeriesName as SeriesName,
	p.MaterialProperty,
	p.ModelName as ModelName,
	p.Weekv,
	p.ItemName as ItemName,
	p.CkSKCQty,
	p.SeriesID,
	p.ItemID,
	p.ShopCode as ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID
	
from
	(
		select
			s.Yearv,
			s.Monthv,
			s.ParentAreaID,
			shop.ShopName as ShopName,
			kind.KindName as KindName,
			series.SeriesName as SeriesName,
			s.MaterialProperty,
			model.ModelName as ModelName,
			s.Weekv,
			item.ItemName as ItemName,
			s.CkSKCQty,
			s.SeriesID,
			s.ItemID,
			shop.ShopCode as ShopCode,
			s.ShopID,
			s.KindID,
			s.ModelID,
            s.CKMoveDeepQty as DeepQty
		from
						ngoods.ngoods_PL_Item_SkcPcs s left join ndw.ndw_Shop shop on s.ExtrSource = shop.ExtrSource and s.CompanyID=shop.CompanyID and
			s.ShopID = shop.ShopID left join ndw.ndw_global_dictionary dict on s.ExtrSource = dict.ExtrSource and s.CompanyID=dict.CompanyID and
			s.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on  s.ExtrSource = kind.ExtrSource and 
			s.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on  s.ExtrSource = model.ExtrSource and 
			s.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on  s.ExtrSource = series.ExtrSource and 
			s.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on s.ExtrSource = item.ExtrSource and 
			s.ItemID = item.ItemID  
			WHERE			 
			   s.CkSKCQty > 0 and s.ExtrSource='{UserInfo.UserExtrSource}' and s.CompanyID='{UserInfo.UserCompanyId}' and
						  s.Yearv={Yearv} and s.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and s.ParentAreaID='{ParentAreaID}' ")}
{(string.IsNullOrWhiteSpace(KindID) ? "" : $" and s.KindID='{KindID}' ")}
{(string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and s.SeriesID='{SeriesID}' ")}
{(string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and s.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrWhiteSpace(ModelID) ? "" : $" and s.ModelID='{ModelID}' ")}
{(string.IsNullOrWhiteSpace(ItemID) ? "" : $" and s.ItemID='{ItemID}' ")}
{(string.IsNullOrWhiteSpace(ShopIDs)
                        ? ""
                        : $" and shop.shopid in {ShopIDs}  ")}  
	) as p 
	group by
	p.Yearv,
	p.Monthv,
	p.ParentAreaID,
	p.ShopName,
	p.KindName,
	p.SeriesName,
	p.MaterialProperty,
	p.ModelName,
	p.ItemName,
	p.Weekv,
	p.CkSKCQty,
	p.SeriesID,
	p.ItemID,
	p.ShopCode,
	p.ShopID,
	p.KindID,
	p.ModelID
)as p cross join 
(select dictName,dictValue from ndw.ndw_global_dictionary
WHERE parentDictValue='全国共性款' and ExtrSource='{UserInfo.UserExtrSource}' and  CompanyID='{UserInfo.UserCompanyId}' 
and isValid=0 ) as k

                    ";

                    #endregion 计算时获取行数
                }
                else
                {
                    #region 查询时获取行数

                    countSql = $@"
SELECT
			count(1) as count
		FROM
			ngoods.ngoods_PL_Item_SkcPcsDetail sp
		LEFT JOIN ndw.ndw_shop shop ON sp.ExtrSource = shop.ExtrSource and sp.CompanyID=shop.CompanyID and
			sp.shopid = shop.shopid
		LEFT JOIN ndw.ndw_global_dictionary dict ON  sp.ExtrSource = dict.ExtrSource and sp.CompanyID=dict.CompanyID and
			sp.ParentAreaID = dict.dictValue left join nods.nods_erp_SD_Mat_Kind kind on  sp.ExtrSource = kind.ExtrSource and 
			sp.KindID = kind.KindID left join nods.nods_erp_SD_Mat_Model model on  sp.ExtrSource = model.ExtrSource and 
			sp.ModelID = model.ModelID left join nods.nods_erp_SD_Mat_Series series on  sp.ExtrSource = series.ExtrSource and 
			sp.SeriesID = series.SeriesID left join nods.nods_erp_SD_Mat_Item item on sp.ExtrSource = item.ExtrSource and 
			sp.ItemID = item.ItemID  
		WHERE			
            sp.ExtrSource='{UserInfo.UserExtrSource}' and sp.CompanyID='{UserInfo.UserCompanyId}' and
			 sp.Yearv={Yearv} and sp.Monthv={Monthv}  

{(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and sp.ParentAreaID = '{ParentAreaID}' ")}
{ (string.IsNullOrWhiteSpace(KindID) ? "" : $" and sp.KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and sp.SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and sp.MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and sp.ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and sp.ItemID='{ItemID}' ")}
                    {
                        (string.IsNullOrWhiteSpace(ShopIDs)
                                               ? ""
                                               : $" and shop.shopid in {ShopIDs} ")}

";
                    #endregion 查询时获取行数
                }

                var cmd = Helper.GetSqlStringCommand(countSql);

                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #region 计算辅助方法

        #region 全部计算

        public IList<PLSplitShopSKCModel.ListModel> ComputeAll(PaperData<IGrouping<string, PLSplitShopSKCModel.ListModel>> result)
        {
            //计算SKC合计,TotalQty
            //ComputeTotalQty(result.Items);
            //计算skc数 和 计算结果,差额
            ComputeCalcQty(result.Items);
            //计算增加/扣减
            result.Items = ComputeAddQty(result.Items);
            //计算skc数
            var list = ComputePirceColorSKCQty(result.Items);
            return list;
        }

        #endregion 全部计算

        #region 计算skc数 和 计算结果,差额

        /// <summary>
        /// 3、计算skc数=skc合计*价格带占比 ；保留两位小数 ;
        /// 4、计算结果=计算skc数四舍五入，取整；
        /// 5、差额=计算结果－计算skc数；
        /// </summary>
        /// <param name="items"></param>
        public void ComputeCalcQty(IList<IGrouping<string, PLSplitShopSKCModel.ListModel>> items)
        {
            //更新计算skc数 和 计算结果,差额
            for (int i = 0; i < items.Count; i++)
            {
                items[i].ToList().ForEach(
                       (p) =>
                       {
                           p.CalcQty = Convert.ToDecimal(MathHelper.Rounding(Convert.ToDouble(p.SKCQty * p.Rate), 2));
                           p.RoundQty = int.Parse(MathHelper.Rounding(Convert.ToDouble(p.CalcQty), 0).ToString());
                           p.DiffValue = p.RoundQty - p.CalcQty;
                       }
                    );
            }
        }


        #endregion 计算skc数 和 计算结果,差额

        #region 计算增加/扣减

        /// <summary>
        /// 增加/扣减 计算
        /// </summary>
        /// <param name="items"></param>
        public IList<IGrouping<string, PLSplitShopSKCModel.ListModel>> ComputeAddQty(IList<IGrouping<string, PLSplitShopSKCModel.ListModel>> list)
        {
            //重新分组,以方便计算,原来是按价格带分组.修改为按周分组.每周包含所有价格带
            var newList = list.SelectMany(p => p).ToList();
            var items = newList.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                            d.ItemID + "_" + d.SKCQty + "_" + d.Weekv
                                            ).ToList();
            //增加/扣减 计算
            for (int i = 0; i < items.Count; i++)
            {
                //判断如果价格带为0,就跳过计算

                //判断以周为单位的计算结果RoundQty合计 和 SKC合计TotalQty 的大小关系
                var result = items[i].Sum(p => p.RoundQty) - items[i].FirstOrDefault().SKCQty;
                //先清空已有的AddQty
                items[i].ToList().ForEach(p => p.AddQty = 0);
                //等于
                if (result == 0)
                {
                    //不做操作,默认已经是0了
                }
                //大于
                else if (result > 0)
                {
                    //8、合计>SKC数时，以周为单位，区域差异款减１；
                    var firstOrDefault = items[i].ToList().FirstOrDefault(p => p.DataFlag == 1);
                    if (firstOrDefault != null)
                        firstOrDefault.AddQty = -1;
                }
                //小于
                else
                {
                    //9、合计 < SKC数时，以周为单位，全国共性款加1；
                    var firstOrDefault = items[i].ToList().FirstOrDefault(p => p.DataFlag == 0);
                    if (firstOrDefault != null)
                        firstOrDefault.AddQty = 1;
                }
            }
            //计算完毕,还原分组.方便下次运算
            var tempList2 = items.SelectMany(p => p).ToList();
            var itemsGrouped = tempList2.GroupBy(
                                        d =>
                                            d.Yearv + "_" + d.Monthv + "_" + d.ParentAreaID + "_" + d.ShopID + "_" +
                                            d.KindID + "_" + d.MaterialProperty + "_" + d.ModelID + "_" + d.SeriesID + "_" +
                                             d.ItemID + "_" + d.DataFlag
                                            ).ToList();
            return itemsGrouped;
        }


        #endregion 计算增加/扣减

        #region 计算skc数



        /// <summary>
        /// 9、计算CKSKCQty SKC数
        /// </summary>
        /// <param name="items"></param>
        public IList<PLSplitShopSKCModel.ListModel> ComputePirceColorSKCQty(IList<IGrouping<string, PLSplitShopSKCModel.ListModel>> items)
        {
            //计算skc数
            for (int i = 0; i < items.Count; i++)
            {
                items[i].ToList().ForEach(
                       (p) =>
                       {
                           p.CkSKCQty = p.RoundQty + p.AddQty;
                       }
                    );
            }

            /*
             * 
            //items为按价格带分组.每个分组中是此小品类中单个价格带一个月的信息
            //更新计算skc数

            //重新分组,以方便计算,原来是按价格带分组.修改为按周分组.每周包含所有价格带
            var newList = items.SelectMany(p => p).ToList();
            var Newitems = newList.GroupBy(
                            d => new
                            {
                                d.Yearv,
                                d.Monthv,
                                d.ParentAreaID,
                                d.ShopID,
                                d.KindID,
                                d.MaterialProperty,
                                d.ModelID,
                                d.SeriesID,
                                d.ItemID
                            }, (ItemName, WeekGroup) => new
                            {
                                ItemName,
                                WeekGroup = WeekGroup.GroupBy(e => e.Weekv, (Weekv, PriceGroup) => new
                                {
                                    Weekv,
                                    PriceGroup
                                })
                            });
            foreach (var item in Newitems)
            {
                //前N周单价格带SKC合计
                Dictionary<string, int> previousTotalQtySumDict = new Dictionary<string, int>();
                foreach (var price in item.WeekGroup.First().PriceGroup)
                {
                    previousTotalQtySumDict.Add(price.DataFlag.ToString(), 0);
                }
                //遍历小品类下边的所有周
                foreach (var week in item.WeekGroup)
                {
                    //遍历周里边的所有价格带
                    foreach (var price in week.PriceGroup)
                    {
                        //为防止skc数出现负数,计算时先判断,
                        //当前价格带的计算结果+增加/扣减-前N周skc的合计 > 0
                        //如果出现负数.就重置为0
                        if (price.RoundQty + price.AddQty - previousTotalQtySumDict[price.DataFlag.ToString()] > 0)
                        {
                            price.PriceColorSKCQty = price.RoundQty + price.AddQty - previousTotalQtySumDict[price.DataFlag.ToString()];
                        }
                        else
                        {
                            price.PriceColorSKCQty = 0;
                        }
                    }

                    #region 校正skc

                    //校正skc
                    //判断以周为单位的计算结果PriceColorSKCQty合计 和 CkSKCQty 的大小关系
                    var result = week.PriceGroup.Sum(p => p.PriceColorSKCQty) - week.PriceGroup.FirstOrDefault().SKCQty;

                    //大于
                    if (result > 0)
                    {
                        //排序
                        //先按差额从大到小排序,
                        //再按价格带从大到小排序
                        var itemsOrdered = week.PriceGroup.Where(p => p.PriceColorSKCQty > 0)
                                                   .OrderByDescending(p => p.DiffValue)
                                                   .ThenByDescending(p => p.DataFlag).ToList();
                        for (int index = 0; index < result; index++)
                        {
                            if (itemsOrdered.Count - 1 < index)
                            {
                                break;
                            }
                            if (0 == itemsOrdered[index].DataFlag)
                            {
                                continue;
                            }
                            if (week.PriceGroup.ToList().FirstOrDefault(p => p.DataFlag == itemsOrdered[index].DataFlag) != null)
                            {
                                week.PriceGroup.ToList().FirstOrDefault(p => p.DataFlag == itemsOrdered[index].DataFlag).PriceColorSKCQty += -1;
                            }
                        }
                    }
                    //小于
                    else if (result < 0)
                    {
                        //排序
                        //先按差额从小到大排序,
                        //再按价格带从小到大排序
                        var itemsOrdered = week.PriceGroup.OrderBy(p => p.DiffValue)
                                                   .ThenBy(p => p.DataFlag).ToList();
                        for (int index = 0; index < Math.Abs(result); index++)
                        {
                            if (itemsOrdered.Count - 1 < index)
                            {
                                break;
                            }
                            if (0 == itemsOrdered[index].DataFlag)
                            {
                                continue;
                            }
                            if (week.PriceGroup.ToList().FirstOrDefault(p => p.DataFlag == itemsOrdered[index].DataFlag) != null)
                            {
                                week.PriceGroup.ToList().FirstOrDefault(p => p.DataFlag == itemsOrdered[index].DataFlag).PriceColorSKCQty += 1;
                            }
                        }
                    }
                    #endregion 校正skc

                    //遍历周里边的所有价格带,把计算出的skc保存到previousTotalQtySumDict
                    foreach (var price in week.PriceGroup)
                    {
                        previousTotalQtySumDict[price.DataFlag.ToString()] += price.PriceColorSKCQty;
                    }
                    
                }
            }

            */

            var list = items.SelectMany(p => p).ToList();
            return list;
        }



        #endregion 计算skc数

        #endregion 计算辅助方法

        #region 修改拆分店铺SKC

        [Obsolete]
        [JsonType]
        [MustLogin]
        [Description("修改拆分店铺SKC")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLSplitShopSKCModel.ListModel>>))]
        public ApiResult Update([ParamDescription("商品区域")] PLSplitShopSKCModel.ModifyModel param)
        {
            try
            {
                if (param == null) return Error("参数对象为空");
                InsertComputedList2DB(param.list);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion 修改拆分店铺SKC

        #region 删除所有的计算结果到数据库
        /// <summary>
        /// 保存所有的计算结果到数据库
        /// </summary>
        /// <param name="list"></param>
        public List<CommandInfo> DeleteComputedList2DB(long Yearv, long Monthv, string ParentAreaID, string KindID,
            string SeriesID, string MaterialProperty, string ModelID, string ItemID, string ShopIDs)
        {

            var cmdList = new List<CommandInfo>();
            #region 删除

            var DeleteSql = $@"

DELETE
from
	ngoods.ngoods_PL_Item_SkcPcsDetail
where
  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and
	   Yearv = '{Yearv}'
	                and Monthv = '{Monthv}'
                    { (string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and ParentAreaID = '{ParentAreaID}' ")}
                    { (string.IsNullOrWhiteSpace(KindID) ? "" : $" and KindID='{KindID}' ")}
                    { (string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and SeriesID='{SeriesID}' ")}
                    { (string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and MaterialProperty='{MaterialProperty}' ")}
                    { (string.IsNullOrWhiteSpace(ModelID) ? "" : $" and ModelID='{ModelID}' ")}
                    { (string.IsNullOrWhiteSpace(ItemID) ? "" : $" and ItemID='{ItemID}' ")}
					                    { (string.IsNullOrWhiteSpace(ShopIDs) ? "" : $@" 	and ShopID In {ShopIDs} ")}
                    ";

            #endregion 删除
            var cmdSave = new CommandInfo()
            {
                CommandText = DeleteSql
            };
            cmdList.Add(cmdSave);
            return cmdList;
        }
        #endregion 删除所有的计算结果到数据库

        #region 保存所有的计算结果到数据库
        /// <summary>
        /// 保存所有的计算结果到数据库
        /// </summary>
        /// <param name="list"></param>
        public void InsertComputedList2DB(IList<PLSplitShopSKCModel.ListModel> list)
        {
            if (list != null && list.Count() > 0)
            {
                var cmdList = new List<CommandInfo>();

                var dtInsert = DataTableHelper.LinqToDataTable(list.Select(
                    p => new
                    {
                        p.Yearv,
                        p.Monthv,
                        p.Weekv,
                        p.ParentAreaID,
                        p.ShopID,
                        p.KindID,
                        p.SeriesID,
                        p.MaterialProperty,
                        p.ModelID,
                        p.ItemID,
                        p.CKMoveDeepQty,
                        p.SKCQty,
                        p.DataFlag,
                        p.CkSKCQty,
                        p.CompanyID,
                        p.ExtrSource,
                        p.MofifyDTM,
                        p.CheckState,
                    }
                    ).ToList());
                Helper.ExecuteSqlTran(cmdList, dtInsert, "ngoods.ngoods_PL_Item_SkcPcsDetail");
            }
        }
        #endregion 保存所有的计算结果到数据库

        #region 批量插入

        void ExecuteSkcCalc(List<CommandInfo> deleteCmd, IList<PLSplitShopSKCModel.ListModel> list)
        {
            var dtInsert = DataTableHelper.LinqToDataTable(list.Select(
               p => new
               {
                   p.Yearv,
                   p.Monthv,
                   p.Weekv,
                   p.ParentAreaID,
                   p.ShopID,
                   p.KindID,
                   p.SeriesID,
                   p.MaterialProperty,
                   p.ModelID,
                   p.ItemID,
                   p.CKMoveDeepQty,
                   p.SKCQty,
                   p.CkSKCQty,
                   p.DataFlag,
                   p.ExtrSource,
                   p.CompanyID,
                   p.MofifyDTM,
                   p.Checker,
               }
               ).ToList());
            Helper.ExecuteSqlTran(deleteCmd, dtInsert, "ngoods.ngoods_PL_Item_SkcPcsDetail");
        }


        #endregion 批量插入


    }

}
